﻿using MySqlConnector;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WinFormsApp1
{
    internal class SQLiteHelper
    {
        static string dbfile = "sql.sqlite";

        //在指定数据库中创建一个table
        public static void CreateTable()
        {
            SQLiteConnection cnn = new SQLiteConnection("data source="+ dbfile);
            cnn.Open();
            SQLiteCommand cmd = new SQLiteCommand("DROP TABLE todo_list;", cnn);
            cmd.ExecuteNonQuery();
            string sql = "CREATE TABLE todo_list (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "category TEXT," +
                "item TEXT," +
                "description TEXT," +
                "responsible_person TEXT," +
                "start_time TEXT," +
                "end_time TEXT);";

            SQLiteCommand cmd1 = new SQLiteCommand(sql, cnn);
            cmd1.ExecuteNonQuery();
            cnn.Close();
        }
        public static SQLiteDataReader DB_List(string s_Table, string s_Field, string s_Where)
        {
            SQLiteConnection cnn = new SQLiteConnection("data source=" + dbfile);
            cnn.Open();
            string sql = string.Format("select {0} from {1} where {2}; ", s_Field, s_Table, s_Where); //sql语言
            SQLiteCommand cmd = new SQLiteCommand(sql, cnn);
            return cmd.ExecuteReader();
        }

        public static int DB_Insert(string s_Table, string s_Field, string s_Data)
        {
            SQLiteConnection cnn = new SQLiteConnection("data source=" + dbfile);
            cnn.Open();

            string s_SQL_Insert =
                          "INSERT INTO " + s_Table + "(" + s_Field + ") " +
                          "VALUES (" + s_Data + ") ;";
            int i_rows = -1;//返回的结果
            SQLiteCommand cmd = new SQLiteCommand(s_SQL_Insert, cnn);
            i_rows = cmd.ExecuteNonQuery();
            cnn.Close();

            return i_rows;
        }

        public static int DB_Update(string s_Table, string s_Set, string s_Where)
        {
            //增加数据的SQL语句
            string s_SQL_Update =
                "UPDATE " + s_Table + " " +
                "SET " + s_Set + " " +
                "WHERE " + s_Where + ";";
            //MessageBox.Show(s_SQL_Update);
            int i_rows = -1;//返回的结果
            SQLiteConnection cnn = new SQLiteConnection("data source=" + dbfile);
            cnn.Open();

            SQLiteCommand cmd = new SQLiteCommand(s_SQL_Update, cnn);
            i_rows = cmd.ExecuteNonQuery();
            cnn.Close();

            return i_rows;
        }
        public static int DB_Delete(string s_Table, string s_Data)
        {
            //增加数据的SQL语句
            string s_SQL_Insert =
                "DELETE FROM " + s_Table + " " +
                "WHERE " + s_Data + ";";
            int i_rows = -1;//返回的结果

            SQLiteConnection cnn = new SQLiteConnection(dbfile);
            cnn.Open();

            SQLiteCommand cmd = new SQLiteCommand(s_SQL_Insert, cnn);
            i_rows = cmd.ExecuteNonQuery();
            cnn.Close();

            return i_rows;
        }
        
    }
}